<?php

App::import('Model', 'Account');
App::import('Model', 'Tweet');
App::import('Model', 'tattlrAccount');
App::import('Model', 'TwitterAccount');
App::import('Model', 'DirectMessage');

//session_start();

class Post extends AppModel {

    var $name = 'Post';
    var $primaryKey = 'postID';

    /**
     * This method checks if the any field in the table named $field has a value of $value
     *
     * @access: default
     * @param: string $field; the coloumn in Posts table
     * @param: $value: the value I need to check
     *
     * @author "Hussein Abo El Seoud"
     */
    function isUnique($field, $value) {
        $fields[$this->name . '.' . $field] = $value;
        $this->recursive = -1;
        if ($this->hasAny($fields)) {
            $this->invalidate('unique_' . $field);
            return false;
        }
        else
            return true;
    }

    /**
     * A function taking the account ID as an input and returns the last tweet
     * related to the account.
     * @param  string $accID
     * @return String release_date of the last tweet posted on twitter.
     * @author Kareem Yousry
     */
    function getLastTweet($accID = NULL) { # Works Great.
        $options['joins'] = array(
            array('table' => 'tweets',
                'alias' => 'Tweet',
                'type' => 'inner',
                'conditions' => array(
                    'Post.postID = Tweet.postID')
                ));

        $options['conditions'] = array('Post.accID' => $accID);
        $options['order'] = array('post.time DESC');

        $res = $this->find('first', $options);

        if ($res != false) {
            return $res['Post']['time'];
        } else {
            return -1; # No such Tweets.
        }
    }

    /*
     * Method to fetch the accounts's tweets, mentions, DMs and retweets from Twitter then save them in the database
     * and then returns all matches (old and new) from the database
     *
     * @access: public
     * @param: string $accountID
     * @return: 3D array 
     *
     * @author "Hussein Abo El Seoud"
     */

    public function fetchFromTwitter($accountID) {
        $tempAccount = new Account();
        $result = $tempAccount->find('first', array('conditions' => array("Account.accID" => $accountID)));
        $username = $result['Account']['twitterName'];

        //Get the id of the last fetched tweet, in order to fetch from after that
        $tattlr = new tattlrAccount();
        $result = $tattlr->find('first', array('conditions' => array("tattlrAccount.accID" => $accountID)));
        if (strlen($result['tattlrAccount']['last_tweet']) > 1) {
            $since_id = $result['tattlrAccount']['last_tweet'];
        } else {
            $since_id = '205455340801302528';
        }

        $this->fetchTweets($accountID, $username, $since_id);

        //Get the max post id and save it in a variable ($since_id2), in order to
        // adjust the last_tweet attribute for the call to the method
        $result = $this->find('all', array('fields' => array('MAX(postID) AS max'), 'conditions' => array("accID" => $accountID)));
        $since_id2 = $result['0']['0']['max'];

        $since_id2 = $this->fetchMentions($accountID, $since_id, $since_id2);
        $since_id2 = $this->fetchDMs($accountID, $since_id, $since_id2);
        $since_id2 = $this->fetchRetweets($accountID, $since_id, $since_id2);

        $tattlr->save(array('accID' => $accountID, 'last_tweet' => $since_id2), false);

        /*
         * Get mentions, messages, favorites and retweets form the database and order them by time.
         * Then put them in the connect variable to be sent to the index.ctp
         */
        return $this->query("select Account.image, Account.name, Account.twitterName, Post.postid, 'Tweet' as type, Post.text, '' as detail, Post.time as timee, Tweet.favorited_by_us, Tweet.retweeted_by_us
                from posts Post join tweets Tweet on Post.postid=Tweet.postid join accounts Account on Post.accID=Account.accID where Post.accID='" . $accountID . "' union select Account.image, Account.name, Account.twitterName, Post.postid, 'Mention' AS type, Post.text,
                concat('Mentioned by account: ',Post.accid) as detail, Post.time as timee, Tweet.favorited_by_us, Tweet.retweeted_by_us from posts Post join tweets Tweet on Post.postid=Tweet.postid join accounts Account on Post.accID=Account.accID where Post.text LIKE 
                '%@" . $username . "%' UNION select Account.image, Account.name, Account.twitterName, Post.postid, 
                'Direct message' as type, Post.text, concat('Sent by account: ',Post.accid) as detail, Post.time as timee, 0 as favorited_by_us, 0 as retweeted_by_us from 
                posts Post join accounts Account on Post.accID=Account.accID join direct_messages Directmessage where Post.postid=Directmessage.postid and 
                sentTo='" . $accountID . "' UNION select Account.image, Account.name, Account.twitterName, p.postid, 
                'Favorite' as type, p.text, concat('Favorited by account: ',p.accid) as detail, f.favouriteDate as timee, t.favorited_by_us, t.retweeted_by_us from 
                twitteraccounts_favourite_tweets f join tweets t on f.postid=t.postid join posts p
                on p.postid=t.postid and p.accid='" . $accountID . "' join accounts Account on f.twitterAccountID=Account.accID UNION select Account.image,
                    Account.name, Account.twitterName, p.postid, 'Retweet' as type, p.text, concat('Retweeted by account: ',p.accid) as detail, r.retweetDate as timee, t.favorited_by_us, t.retweeted_by_us from 
                twitteraccounts_retweet_tweets r join tweets t on r.postid=t.postid join posts p
                on p.postid=t.postid and p.accid='" . $accountID . "' join accounts Account on r.twitterAccountID=Account.accID order by timee desc");
    }

    /*
     * Method to fetch tweets from twitter and insert them into the database
     *
     * @access: public
     * @param: string $accountID
     * @param: string $username
     * @param: string $since_id
     *
     * @author "Hussein Abo El Seoud"
     */

    public function fetchTweets($accountID, $username, $since_id) {
        $tempAccount = new Account();
        //Fetch tweets
        $status = $tempAccount->connectionRequest($accountID)->get('statuses/user_timeline', array('screen_name' => $username, 'since_id' => $since_id));
        //loop on my tweets
        foreach ($status as $json) {
            $post = (array) $json;
            $user = (array) $post['user'];
            //formate the date
            $mysql_format = date("Y-m-d H:i:s", strtotime($post['created_at']));
            //see if the tweet already exists, if not add it to our tables (Posts and tweets)
            if ($this->isUnique("postID", $post['id_str'])) {
                //save in Posts table
                $this->set(array('postID' => $post['id_str'], 'accID' => $user['id_str'], 'text' => $post['text'], 'time' => $mysql_format));
                if ($this->save()) {
                    //save in Tweets table
                    $tweet = new Tweet();
                    $tweet->set(array('postID' => $post['id_str'], 'retweets' => $post['retweet_count'], 'isMention' => 0, 'replyTo' => $post['in_reply_to_status_id_str'], 'favorited_by_us' => $post['favorited'], 'retweeted_by_us' => $post['retweeted']));
                    $tweet->save();
                }
            }
        }
    }

    /*
     * Method to fetch mentions from twitter and insert them into the database
     *
     * @access: public
     * @param: string $accountID
     * @param: string $since_id
     * @param: string $since_id2
     * @return: string (the updated $since_id2)
     *
     * @author "Hussein Abo El Seoud"
     */

    public function fetchMentions($accountID, $since_id, $since_id2) {
        $tempAccount = new Account();
        //Fetch mentions from Twitter
        $status = $tempAccount->connectionRequest($accountID)->get('statuses/mentions', array('since_id' => $since_id));
        //loop on mentions
        foreach ($status as $json) {
            $post = (array) $json;
            $user = (array) $post['user'];

            // see if the accounts already exists, if not add him to our tables(twitterAccounts and accounts)
            if ($tempAccount->isUnique("accID", $user['id_str'])) {
                $tempAccount->save(array('accID' => $user['id_str'], 'twitterName' => $user['screen_name'], 'image' => $user['profile_image_url'], 'name' => $user['name']), false);
                $twitter = new twitterAccount();
                $twitter->save(array('accID' => $user['id_str']), false);
            }
            //see if the tweet already exists, if not add it to our tables (Posts and tweets)
            if ($this->isUnique("postID", $post['id_str'])) {
                //formate the date
                $mysql_format = date("Y-m-d H:i:s", strtotime($post['created_at']));
                //save in Posts table
                $this->set(array('postID' => $post['id_str'], 'accID' => $user['id_str'], 'text' => $post['text'], 'time' => $mysql_format));
                if ($this->save()) {
                    //save in Tweets table
                    $tweet = new Tweet();
                    $tweet->set(array('postID' => $post['id_str'], 'retweets' => $post['retweet_count'], 'isMention' => 1, 'replyTo' => $post['in_reply_to_status_id_str'], 'favorited_by_us' => $post['favorited'], 'retweeted_by_us' => $post['retweeted']));
                    $tweet->save();
                }
            }
            //Compare each post id to $since_id2, if greater set 3since_id2 to it
            if ((int) $post['id_str'] > (int) $since_id2) {
                $since_id2 = $post['id_str'];
            }
        }
        return $since_id2;
    }

    /*
     * Method to fetch DMs from twitter and insert them into the database
     *
     * @access: public
     * @param: string $accountID
     * @param: string $since_id
     * @param: string $since_id2
     * @return: string (the updated $since_id2)
     *
     * @author "Hussein Abo El Seoud"
     */

    public function fetchDMs($accountID, $since_id, $since_id2) {
        $tempAccount = new Account();
        //Fetch DMs from Twitter
        $status = $tempAccount->connectionRequest($accountID)->get('direct_messages', array('since_id' => $since_id));
        //loop on DMs
        foreach ($status as $json) {
            $post = (array) $json;
            $user = (array) $post['sender'];
            //see if the accounts already exists.
            //if not add him to our tables(twitterAccounts and accounts).
            if ($tempAccount->isUnique("accID", $user['id_str'])) {
                $tempAccount->save(array('accID' => $user['id_str'], 'twitterName' => $user['screen_name'], 'image' => $user['profile_image_url'], 'name' => $user['name']), false);
                $twitter = new twitterAccount();
                $twitter->save(array('accID' => $user['id_str']), false);
            }
            //see if the tweet already exists, if not add it to our tables (Posts and tweets)
            if ($this->isUnique("postID", $post['id_str'])) {
                //formate the date
                $mysql_format = date("Y-m-d H:i:s", strtotime($post['created_at']));
                //save in Posts table
                $this->set(array('postID' => $post['id_str'], 'accID' => $user['id_str'], 'text' => $post['text'], 'time' => $mysql_format));
                if ($this->save()) {
                    //save in directMessages table
                    $dm = new DirectMessage();
                    $dm->save(array('postID' => $post['id_str'], 'sentTo' => $post['recipient_id']), false);
                }
            }
            //Compare each post id to $since_id2, if greater set 3since_id2 to it
            if ((int) $post['id_str'] > (int) $since_id2) {
                $since_id2 = $post['id_str'];
            }
        }
        return $since_id2;
    }

    /*
     * Method to fetch retweets from twitter and insert them into the database
     *
     * @access: public
     * @param: string $accountID
     * @param: string $since_id
     * @param: string $since_id2
     * @return: string (the updated $since_id2)
     *
     * @author "Hussein Abo El Seoud"
     */

    public function fetchRetweets($accountID, $since_id, $since_id2) {
        $tempAccount = new Account();
        //Fetch retweets from Twitter
        $status = $tempAccount->connectionRequest($accountID)->get('statuses/retweets_of_me', array('since_id' => $since_id));
        //loop on retweets
        foreach ($status as $json) {
            $post = (array) $json;
            $user1 = (array) $post['user'];
            $accounts = $tempAccount->connectionRequest($accountID)->get('statuses/' . $post['id_str'] . '/retweeted_by');
            foreach ($accounts as $json2) {
                $user = (array) $json2;
                //see if the account already exists, if not add him to our tables(twitterAccounts and accounts).
                if ($tempAccount->isUnique("accID", $user['id_str'])) {
                    $tempAccount->save(array('accID' => $user['id_str'], 'twitterName' => $user['screen_name'], 'image' => $user['profile_image_url'], 'name' => $user['name']), false);
                    $twitter = new twitterAccount();
                    $twitter->save(array('accID' => $user['id_str']), false);
                }
                //format the date
                $mysql_format = date("Y-m-d H:i:s", strtotime($post['created_at']));
                //see if the tweet already exists, if not add it to our tables (Posts and tweets)
                if ($this->isUnique("postID", $post['id_str'])) {
                    //save in Posts table
                    $this->set(array('postID' => $post['id_str'], 'accID' => $user1['id_str'], 'text' => $post['text'], 'time' => $mysql_format));
                    if ($this->save()) {
                        //save in Tweets table
                        $tweet = new Tweet();
                        $tweet->set(array('postID' => $post['id_str'], 'retweets' => $post['retweet_count'], 'isMention' => 0, 'replyTo' => $post['in_reply_to_status_id_str'], 'favorited_by_us' => $post['favorited'], 'retweeted_by_us' => $post['retweeted']));
                        $tweet->save();
                    }
                }
                //see if the retweet already exists, if not add it to our table
                $result = $this->query("select count(*) from twitteraccounts_retweet_tweets where twitterAccountID='" . $user['id_str'] . "' and postID=" . $post['id_str']);
                $c = $result['0']['0']['count(*)'];
                if ($c == 0) {
                    //save in twitteraccounts_retweet_tweets table
                    $this->query("insert into twitteraccounts_retweet_tweets (twitterAccountID, postID, retweetDate) values ('" . $user['id_str'] . "','" . $post['id_str'] . "','" . $mysql_format . "')");
                }
            }
            //Compare each post id to $since_id2, if greater set 3since_id2 to it
            if ((int) $post['id_str'] > (int) $since_id2) {
                $since_id2 = $post['id_str'];
            }
        }
        return $since_id2;
    }

    /*
     * Description of "getEventTweets"
     * @param  'eventID' as $id
     *
     * description of the function:
     * This function's purpose is to retrieve all the text for the tweets
     * related to a certain event,this function links the two tables
     * 'posts' & 'tweets' together by a joint based on the 'postID' field
     * and including only the records that have an 'eventID' value equals
     * to the value of the $id parameter
     * @author "Mo'men el Sawy"
     */

    function getEventTweets($id) {
        $count = $this->find('count');
        if ($count != 0) {
            $options['joins'] = array(//joining the two tables posts and tweets
                array('table' => 'tweets',
                    'alias' => 'Tweet',
                    'type' => 'INNER',
                    'conditions' => array(
                        'Tweet.postID = Post.postID',
                        'Tweet.eventID ' => $id
                    )
                )
            );

            $result = $this->find('all', $options);

            return $result;
        } else {
            return -1;
        }
    }

    /*
     * Description of "getEventTweets"
     * @param  'eventID' as $id
     *
     * description of the function:
     * This function's purpose is to retrieve all the text for the tweets
     * related to a certain event,this function links the two tables
     * 'posts' & 'tweets' together by a joint based on the 'postID' field
     * and including only the records that have an 'eventID' value equals
     * to the value of the $id parameter
     * @author "Mo'men el Sawy"
     */

    function getMentions() {
        $count = $this->find('count');
        if ($count != 0) {
            $options['joins'] = array(
                array('table' => 'tweets',
                    'alias' => 'Tweet',
                    'type' => 'INNER',
                    'conditions' => array(
                        'Tweet.postID = Post.postID',
                        'Tweet.isMention ' => 1
                    )
                )
            );
            return $this->find('all', ($options));
        } else {
            return -1; // the database is still empty no records added yet
        }
    }

    /*
     * Description of getPostText
     * $postID
     * Selects the text of a post(in table posts) in the database
     * @author Ahmed El-Fouly
     */

    public function getPostText($postID) {
        $data = $this->find('first', array('conditions' => array('postID' => $postID)));
        return $data['Post']['text'];
    }

    /**
     * Description of function "export"
     * @param string $postID
     * @return array of tweets 
     * 
     * Description :
     * A function taking the post ID as an input checks if there are any tweets exist in the database 
     * then it returns an array of tweets with all their data.  
     * 
     * @author "Omar Elshal"
     */
    public function export($postID) {
        $count = $this->find('count');
        if ($count != 0) {
            $result = $this->query("SELECT * FROM posts, tweets, users, accounts WHERE posts.postID = tweets.postID AND users.userID = posts.userID
                                            AND accounts.accID = posts.accID ");
            return $result;
        }
        else
            return 0;
    }

    /*
     * Description of "deleteTweets($postID)"
     * @param: $postID
     *
     * Description of the function:
     * This function deletes the selected tweet and all of its details from the database completely
     *
     * @author "Omar Safwany"
     */

    public function deleteTweets($postID) {
        $this->query("delete from twitteraccounts_retweet_tweets where postID = '" . $postID . "'");
        $this->query("delete from twitteraccounts_favourite_tweets where postID = '" . $postID . "'");
        $this->query("delete from users_favourite_tweets where postID = '" . $postID . "'");
        $this->query("delete from users_retweet_tweets where postID = '" . $postID . "'");
        $result = $this->query("select * from tasks where tasks.postID = '" . $postID . "'");
        foreach ($result as $r) {
            $id = $r['tasks']['taskID'];
            $this->query("delete from tasks_has_users where tasks_has_users.taskID = " . $id);
            $this->query("delete from messages where messages.taskID = " . $id);
            $this->query("delete from tasks where tasks.taskID = " . $id);
        }
        $this->query("delete from tweets where tweets.postID = " . $postID);
        $this->delete($postID);
    }
    public function deleteThisPost($postID ){

        return $this->delete($postID);
    }

	/*
     * Description of "deleteAll($accID)"
     * @param: $postID
     *
     * Description of the function:
     * This function deletes all tweets of the selected user from the database completely
     *
     * @author "Omar Safwany"
     */

    public function deleteAll($userID) {
        $this->query("delete from users_favourite_tweets where userID = '" . $userID . "'");
		$this->query("delete from users_favourite_tweets where userID = '" . $userID . "'");
		$this->query("delete from tasks_has_users where tasks_has_users.userID = '" . $userID ."'");
		$result = $this->query("select * from tasks, accounts_has_users where tasks.accID = accounts_has_users.accID and accounts_has_users.userID = '" . $userID . "'");
		$result1 = $this->query("select * from tasks, accounts_has_users where tasks.accID = accounts_has_users.accID and accounts_has_users.userID = '" . $userID . "'");
                foreach ($result1 as $r) {
            $id = $r['tasks']['taskID'];
            $this->query("delete from messages where messages.taskID = " . $id);
            $this->query("delete from tasks where tasks.taskID = " . $id);
        }
        $result2 = $this->query("select * from tweets inner join posts on tweets.postID = posts.postID and posts.userID = " . $userID);
		foreach($result2 as $r)
		{
			$x = $r['tweets']['postID'];
			$this->query("delete from tweets where tweets.postID = '" . $x . "'");
		}
		$this->query("delete from posts where posts.userID = " . $userID);
    }
}

?>
